﻿/**
* QSmart Andon
*
* 2012-10-10
* 
* steven.li
*
* Copyright(c) 2012 All Rights Reserved. Taiyou Tech(GUANGZHOU) Co.,Ltd.  (广州太友科技) [ sales@gztaiyou.com]. 
*
*/
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Text;
using FX.CC.Andon.Entity.SystemManage;
using FX.CC.Andon.Utility.Common;
using FX.CC.Andon.Utility.DataBase;

namespace FX.CC.Andon.Service.SystemManage
{
    public class SysMenuService : DbHelperBase
    {
        public List<SysMenuEntity> LoadSysMenuList(string loginCode)
        {
            var strSql = new StringBuilder();
            var list = new List<SysMenuEntity>();
            object obj = DBNull.Value;
            strSql.Append("SELECT A.MenuID,A.MenuName,A.URL,A.ParentMenuID FROM UT_SysMenu AS A ");
            strSql.Append(" INNER JOIN UT_RoleRight AS B ON A.MenuID = B.MenuID  ");
            strSql.Append(" INNER JOIN UT_Role AS C ON B.RoleID = C.RoleID ");
            strSql.Append(" INNER JOIN UT_User AS D ON C.RoleID = D.RoleID ");
            strSql.Append(" WHERE D.LoginCode=@LoginCode ORDER BY A.[MenuIDX] ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@LoginCode", loginCode);
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new SysMenuEntity();
                            if (reader["MenuID"] != DBNull.Value)
                                model.MenuID = new Guid(reader["MenuID"].ToString());
                            if (reader["ParentMenuID"] != DBNull.Value)
                                model.ParentMenuID = new Guid(reader["ParentMenuID"].ToString());
                            model.MenuName = reader["MenuName"] == obj ? null : Convert.ToString(reader["MenuName"]);
                            model.URL = reader["URL"] == obj ? null : Convert.ToString(reader["URL"]);
                            list.Add(model);
                        }
                    }

                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        public List<SysMenuEntity> GetSysMenuList()
        {
            var strSql = new StringBuilder();
            var list = new List<SysMenuEntity>();
            object obj = DBNull.Value;
            strSql.Append("SELECT MenuID,MenuName,URL,ParentMenuID FROM UT_SysMenu");
            strSql.Append("  ORDER BY [MenuIDX] ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new SysMenuEntity();
                            if (reader["MenuID"] != DBNull.Value)
                                model.MenuID = new Guid(reader["MenuID"].ToString());
                            if (reader["ParentMenuID"] != DBNull.Value)
                                model.ParentMenuID = new Guid(reader["ParentMenuID"].ToString());
                            model.MenuName = reader["MenuName"] == obj ? null : Convert.ToString(reader["MenuName"]);
                            list.Add(model);
                        }
                    }

                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        /// <summary>
        /// 获取父节点菜单
        /// </summary>
        /// <returns></returns>
        public IList<SysMenuEntity> GetSysMenuParentNode()
        {
            var list = new List<SysMenuEntity>();
            object obj = DBNull.Value;
            const string strSql = @"SELECT [MenuID],[MenuName] FROM UT_SysMenu where ParentMenuID IS NULL ORDER BY [MenuIDX] asc";

            using (var cmd = Db.GetSqlStringCommond(strSql))
            {
                try
                {
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new SysMenuEntity
                            {
                                MenuID = new Guid(reader["MenuID"].ToString()),
                                ParentMenuID = new Guid("00000000-0000-0000-0000-000000000000"),
                                MenuName =
                                    reader["MenuName"] == obj
                                        ? null
                                        : Convert.ToString(reader["MenuName"])
                            };
                            list.Add(model);

                        }
                    }

                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        /// <summary>
        /// 获取总记录数
        /// </summary>
        /// <param name="menuId"></param>
        /// <returns></returns>
        public int GetSysMenuChildNodeCount(string menuId)
        {
            var strSql = new StringBuilder();
            strSql.Append("select  count(*) AS [count] FROM UT_SysMenu where ParentMenuID=@MenuID");
            var count = 0;
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@MenuID", menuId);
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            count = Convert.ToInt32(reader["count"]);
                        }
                    }

                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return count;
            }
        }

        /// <summary>
        /// 联动获取子节点
        /// </summary>
        /// <param name="qcmodel"></param>
        /// <returns></returns>
        public List<SysMenuEntity> GetSysMenuChildNode(SysMenuQcEntity qcmodel)
        {
            var list = new List<SysMenuEntity>();
            var strSql = new StringBuilder();
            object obj = DBNull.Value;

            int start = qcmodel.PageSize * qcmodel.PageIndex + 1;
            int end = qcmodel.PageSize + start - 1;

            strSql.Append(" SELECT Tmp.* From ( ");
            strSql.Append(" SELECT row_number() over (order by MenuIDX) as idx,[MenuID],[MenuName],[URL],[ParentMenuID],[Remark],[MenuIDX] FROM UT_SysMenu ");
            strSql.Append(" WHERE ParentMenuID=@ParentMenuID ) Tmp ");
            strSql.Append(" Where Tmp.idx between " + start + " and " + end);

            if (qcmodel.SortField != "" && qcmodel.SortOrder != "")
            {
                strSql.Append(" Order by " + qcmodel.SortField + " " + qcmodel.SortOrder);
            }

            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@ParentMenuID", qcmodel.MenuID);

                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new SysMenuEntity();
                            if (reader["MenuID"] != DBNull.Value)
                                model.MenuID = new Guid(reader["MenuID"].ToString());
                            model.MenuName = reader["MenuName"] == obj ? null : Convert.ToString(reader["MenuName"]);
                            model.URL = reader["URL"] == obj ? null : Convert.ToString(reader["URL"]);
                            if (reader["ParentMenuID"] != DBNull.Value)
                                model.ParentMenuID = new Guid(reader["ParentMenuID"].ToString());
                            model.Remark = reader["Remark"] == obj ? null : Convert.ToString(reader["Remark"]);
                            if (reader["MenuIDX"] != DBNull.Value)
                                model.MenuIDX = Convert.ToInt32(reader["MenuIDX"]);
                            list.Add(model);
                        }
                    }

                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        /// <summary>
        /// 删除菜单
        /// </summary>
        /// <param name="menuId"></param>
        public bool Remove(string menuId)
        {
            Guid mID = new Guid(menuId);
            var strSql = new StringBuilder();
            strSql.Append(" Delete From UT_SysMenu Where MenuID=@mID ");
            var result = false;
            using (DbCommand cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@mID", DbType.Guid, mID);
                    result = Db.ExecuteNonQuery(cmd) > 0;
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        /// <summary>
        /// 获取单条菜单信息
        /// </summary>
        /// <param name="menuId"></param>
        /// <returns></returns>
        public SysMenuEntity GetSysMenuInfo(string menuId)
        {
            var model = new SysMenuEntity();
            object obj = DBNull.Value;
            var strSql = new StringBuilder();
            strSql.Append(" SELECT [MenuID],[MenuName],[URL],[ParentMenuID],[Remark],[MenuIDX] FROM UT_SysMenu where MenuID=@MenuID ");
            using (DbCommand cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                Db.AddInParameter(cmd, "@MenuID", menuId);
                try
                {
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            if (reader["MenuID"] != DBNull.Value)
                                model.MenuID = new Guid(reader["MenuID"].ToString());
                            model.MenuName = reader["MenuName"] == obj ? null : Convert.ToString(reader["MenuName"]);
                            model.URL = reader["URL"] == obj ? null : Convert.ToString(reader["URL"]);
                            if (reader["ParentMenuID"] != DBNull.Value)
                                model.ParentMenuID = new Guid(reader["ParentMenuID"].ToString());
                            model.Remark = reader["Remark"] == obj ? null : Convert.ToString(reader["Remark"]);
                            if (reader["MenuIDX"] != DBNull.Value)
                                model.MenuIDX = Convert.ToInt32(reader["MenuIDX"]);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return model;
            }
        }

        /// <summary>
        /// 添加菜单
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool AddSysMenu(SysMenuEntity model)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" INSERT INTO UT_SysMenu ([MenuID],[MenuName],[URL],[ParentMenuID],[Remark],[MenuIDX]) ");
            strSql.Append(" VALUES(@MenuID,@MenuName,@URL,@ParentMenuID,@Remark,@MenuIDX) ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@MenuID", model.MenuID);
                    Db.AddInParameter(cmd, "@MenuName", model.MenuName);
                    Db.AddInParameter(cmd, "@URL", model.URL);
                    Db.AddInParameter(cmd, "@ParentMenuID", model.ParentMenuID);
                    Db.AddInParameter(cmd, "@Remark", model.Remark);
                    Db.AddInParameter(cmd, "@MenuIDX", model.MenuIDX);
                    if (Db.ExecuteNonQuery(cmd) > 0)
                    {
                        result = true;
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        /// <summary>
        /// 修改菜单
        /// </summary>
        /// <param name="model"></param>
        public bool ModifySysMenu(SysMenuEntity model)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" UPDATE UT_SysMenu SET [MenuName] = @MenuName,[URL] = @URL,[Remark] = @Remark,[MenuIDX] = @MenuIDX WHERE [MenuID] = @MenuID ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@MenuName", model.MenuName);
                    Db.AddInParameter(cmd, "@URL", model.URL);
                    Db.AddInParameter(cmd, "@Remark", model.Remark);
                    Db.AddInParameter(cmd, "@MenuIDX", model.MenuIDX);
                    Db.AddInParameter(cmd, "@MenuID", model.MenuID);
                    if (Db.ExecuteNonQuery(cmd) > 0)
                    {
                        result = true;
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }


        public bool AddParentMenuNode(SysMenuEntity model)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" INSERT INTO UT_SysMenu ([MenuID],[MenuName],[Remark],[MenuIDX]) ");
            strSql.Append(" VALUES(@MenuID,@MenuName,@Remark,@MenuIDX) ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@MenuID", model.MenuID);
                    Db.AddInParameter(cmd, "@MenuName", model.MenuName);
                    Db.AddInParameter(cmd, "@Remark", model.Remark);
                    Db.AddInParameter(cmd, "@MenuIDX", model.MenuIDX);
                    if (Db.ExecuteNonQuery(cmd) > 0)
                    {
                        result = true;
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public bool ModifyParentMenuNode(SysMenuEntity model)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" UPDATE UT_SysMenu SET [MenuName] = @MenuName,[Remark] = @Remark,[MenuIDX] = @MenuIDX WHERE [MenuID] = @MenuID ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@MenuName", model.MenuName);
                    Db.AddInParameter(cmd, "@Remark", model.Remark);
                    Db.AddInParameter(cmd, "@MenuIDX", model.MenuIDX);
                    Db.AddInParameter(cmd, "@MenuID", model.MenuID);
                    if (Db.ExecuteNonQuery(cmd) > 0)
                    {
                        result = true;
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public bool RemoveChildMenuNode(string menuId)
        {
            var strSql = new StringBuilder();
            strSql.Append(" Delete From UT_SysMenu Where ParentMenuID=@MenuID ");
            var result = false;
            using (DbCommand cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@MenuID", menuId);
                    result = Db.ExecuteNonQuery(cmd) > 0;
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }
    }
}
